﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;

namespace Trackr.Core.DataProvider.SQL
{
	public class SQLiteUtility<T> where T : class
	{
		private SQLiteConnection _sqLiteConnection;
		private object _lock = new object();
		public SQLiteUtility(string databasePath)
		{
			if (!System.IO.File.Exists(databasePath))
			{
				SQLiteConnection.CreateFile(databasePath);
			}
			string connString = string.Format("Data Source={0};Version=3;", databasePath);
			_sqLiteConnection = new SQLiteConnection(connString);
			_sqLiteConnection.Open();
			_sqLiteConnection.Update += sqLiteConnection_Update;
			TryCreateTable(typeof(T));
		}

		void sqLiteConnection_Update(object sender, UpdateEventArgs e)
		{
			//TODO: only clear affected cache items, need to get correct cache key...
			CacheUtility.Clear();
		}
		public IEnumerable<T> ExecuteReader(IQuery query)
		{
			return ExecuteReader(query.ToSql());
		}
		public IEnumerable<T> ExecuteReader(string sql)
		{
			using (SQLiteCommand cmd = new SQLiteCommand(sql, _sqLiteConnection))
			using (SQLiteDataReader reader = cmd.ExecuteReader())
			{
				while (reader.Read())
				{
					T obj = PopulateObject(reader);
					yield return obj;
				}
			}
		}
		private T PopulateObject(SQLiteDataReader reader)
		{
			T obj = Activator.CreateInstance<T>();
			foreach (var prop in typeof(T).GetProperties())
			{
				try
				{
					object val = reader[prop.Name];
					if (val is System.DBNull)
						continue;
					if (val.GetType() == prop.PropertyType)
					{
						prop.SetValue(obj, val, null);
					}
					else
					{
						val = Convert.ChangeType(val, prop.PropertyType);
						prop.SetValue(obj, val, null);
					}
				}
				catch { }
			}
			return obj;
		}

		public int ExecuteCommand(IQuery query)
		{
			return ExecuteCommand(query.ToSql());
		}

		public int ExecuteCommand(string sqlQuery)
		{
			sqlQuery = sqlQuery.Replace("\r", string.Empty).Replace("\n", string.Empty);
			if (_sqLiteConnection.State == System.Data.ConnectionState.Closed)
				_sqLiteConnection.Open();
			lock (_lock)
			{
				using (SQLiteCommand cmd = new SQLiteCommand(sqlQuery, _sqLiteConnection))
				{
					return cmd.ExecuteNonQuery();
				}
			}
		}

		private void TryCreateTable(Type type)
		{
			StringBuilder cmdTxt = new StringBuilder();
			cmdTxt.Append(string.Format("CREATE TABLE IF NOT EXISTS [{0}]", type.Name));
			cmdTxt.Append("(");
			cmdTxt.Append("[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ");
			var props = type.GetProperties().Where(p => p.Name.ToLower() != "id");
			string columns = string.Join(", ", props.Select(p => string.Format("[{0}] {1}", p.Name, p.PropertyType.GetDbType())).ToArray());
			columns.Trim(',');
			cmdTxt.Append(columns);
			cmdTxt.Append(");");
			//string sqlTable = "CREATE TABLE IF NOT EXISTS [TrackrData]([id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[task] NVARCHAR(500) NOT NULL,[startDate] DATETIME NOT NULL,[endDate] DATETIME NULL);";
			ExecuteCommand(cmdTxt.ToString());
		}

		internal void Create(string _tableName)
		{
			TryCreateTable(typeof(T));
		}
	}
}
